package com.union.core.organization.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hnjowe.webdb.DBOperation;
import com.union.core.organization.dao.PersonnelDao;
import com.union.core.organization.model.Personnel;

public class PersonnelDaoImpl implements PersonnelDao{
	
	private Personnel personnel;
	private DBOperation opera;
	private static Logger log = Logger.getLogger(PersonnelDaoImpl.class.getName());
	public PersonnelDaoImpl(){
		
	}
	
	/**
	 * 
	 * @param personnel_id
	 * @param personnel_name
	 * @param personnel_age
	 * @param personnel_sex
	 * @param personnel_position
	 * @param organization_name
	 * @param paramName
	 * @param paramValue
	 * @param orderByName
	 * @param start
	 * @param pageSize
	 * @return
	 */
	public List<Personnel> queryList(int personnel_id,String personnel_name,String birthday,String personnel_sex,String personnel_position,
			String organization_name, String department,String paramName, String paramValue, String orderByName,int start,int pageSize,int organization_id,String getChildLst){

		
		String sqlstr1 = ""; 	//条件1
		String sqlstr2 = ""; 	//条件2
		String sqlstr3 = ""; 	//条件3
		String sqlstr4 = ""; 	//条件4
		String sqlstr5 = ""; 	//条件5
		String sqlstr6 = ""; 	//条件6
		String sqlstr7 = ""; 	//条件7
		String sqlstr8 = ""; 	//条件8
		String sqlstr9 = ""; 	//条件9
		String sqlstr10 = ""; 	//条件9
		String orderbySql = ""; //排序条件
		
		if (personnel_id!=0) sqlstr1 = " and personnel_id="+personnel_id;
		if (personnel_name!=null) sqlstr2 = " and personnel_name='"+personnel_name+"'";
		if (birthday!=null) sqlstr3 = " and birthday='"+birthday+"'";
		if (personnel_sex!=null) sqlstr4 = " and personnel_sex='"+personnel_sex+"'";
		if (personnel_position!=null) sqlstr5 = " and personnel_position='"+personnel_position+"'";
		if (organization_name!=null) sqlstr6 = " and organization_name='"+organization_name+"'";
		if (department!=null) sqlstr8 = " and department='"+department+"'";
		if (getChildLst!=null) sqlstr10 = " and organization_id in ("+getChildLst+")";
		
		if (paramName != null && paramValue != null) {
			if(paramName.equals("personnel_id")) sqlstr7 = " and personnel_id = "+paramValue;
			if(paramName.equals("personnel_name")) sqlstr7 = " and personnel_name like '%"+paramValue+"%'";
			if(paramName.equals("birthday")) sqlstr7 = " and birthday like '%"+paramValue+"%'";
			if(paramName.equals("personnel_sex")) sqlstr7 = " and personnel_sex like '%"+paramValue+"%'";
			if(paramName.equals("personnel_position")) sqlstr7 = " and personnel_position like '%"+paramValue+"%'";
			if(paramName.equals("organization_name")) sqlstr7 = " and organization_name like '%"+paramValue+"%'";
			if(paramName.equals("department")) sqlstr7 = " and department like '%"+paramValue+"%'";
		}
		if (organization_id!=0) sqlstr9 = " and organization_id="+organization_id;
		if (orderByName != null&& !"".equals(orderByName)) {
			String str[] = orderByName.split("-");			
			if (str[1].equals("UP"))
				orderbySql = " order by " + str[0];
			if (str[1].equals("DOWN"))
				orderbySql = " order by " + str[0] + " desc";
		} else {
			orderbySql = " order by personnel_id asc";
		}
		
		List<Personnel> personnelList = new ArrayList<Personnel>();
		
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "

					+ "personnel_id,personnel_name,birthday,personnel_sex,personnel_phone,personnel_address,personnel_position," 
					+ "organization_id,organization_name,pid,organization_parent,add_name,add_time,updae_name,update_time,remarks," 
					+ "department,email,nation,political_status,educational_background,major,native_place,id_num,issuing_authority,account_id,account_name"

					+ " from personnel where personnel_id>0"+ sqlstr10+ sqlstr1 + sqlstr2 + sqlstr3 + sqlstr4 + sqlstr5+ sqlstr6 

					+ sqlstr7 + sqlstr8 + sqlstr9 + orderbySql  

			        + " LIMIT ?,?";
			opera.prepStatment(sql);
			//opera.setString(1, getChildLst);
			opera.setInt(1, start);
			opera.setInt(2, pageSize);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				personnel = new Personnel();
				personnel.setPersonnel_id(res.getInt(1));
				personnel.setPersonnel_name(res.getString(2));
				personnel.setBirthday(res.getString(3));
				personnel.setPersonnel_sex(res.getString(4));
				personnel.setPersonnel_phone(res.getString(5));
				personnel.setPersonnel_address(res.getString(6));
				personnel.setPersonnel_position(res.getString(7));
				personnel.setOrganization_id(res.getInt(8));
				personnel.setOrganization_name(res.getString(9));
				personnel.setPid(res.getInt(10));
				personnel.setOrganization_parent(res.getString(11));
				personnel.setAdd_name(res.getString(12));
				personnel.setAdd_time(res.getString(13));
				personnel.setUpdae_name(res.getString(14));
				personnel.setUpdate_time(res.getString(15));
				personnel.setRemarks(res.getString(16));		
				personnel.setDepartment(res.getString(17));
				personnel.setEmail(res.getString(18));
				personnel.setNation(res.getString(19));
				personnel.setPolitical_status(res.getString(20));
				personnel.setEducational_background(res.getString(21));
				personnel.setMajor(res.getString(22));
				personnel.setNative_place(res.getString(23));
				personnel.setId_num(res.getString(24));
				personnel.setIssuing_authority(res.getString(25));
				personnel.setAccount_id(res.getInt(26));
				personnel.setAccount_name(res.getString(27));
				personnelList.add(i, personnel);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}	
		return personnelList;
	}
	
	/**
	 * 
	 * @param personnel_id
	 * @param personnel_name
	 * @param personnel_age
	 * @param personnel_sex
	 * @param personnel_position
	 * @param organization_name
	 * @param paramName
	 * @param paramValue
	 * @param orderByName
	 * @return
	 */

	public int ListCount(int personnel_id,String personnel_name,String birthday,String personnel_sex,String personnel_position,
			String organization_name, String department, String paramName, String paramValue, int organization_id,String getChildLst){
		String sqlstr1 = ""; 	//条件1
		String sqlstr2 = ""; 	//条件2
		String sqlstr3 = ""; 	//条件3
		String sqlstr4 = ""; 	//条件4
		String sqlstr5 = ""; 	//条件5
		String sqlstr6 = ""; 	//条件6
		String sqlstr7 = ""; 	//条件7
		String sqlstr8 = ""; 	//条件8
		String sqlstr9 = ""; 	//条件9
		String sqlstr10= ""; 	//条件9

		if (personnel_id!=0) sqlstr1 = " and personnel_id="+personnel_id;
		if (personnel_name!=null) sqlstr2 = " and personnel_name='"+personnel_name+"'";
		if (birthday!=null) sqlstr3 = " and birthday='"+birthday+"'";
		if (personnel_sex!=null) sqlstr4 = " and personnel_sex='"+personnel_sex+"'";
		if (personnel_position!=null) sqlstr5 = " and personnel_position='"+personnel_position+"'";
		if (organization_name!=null) sqlstr6 = " and organization_name='"+organization_name+"'";
		if (department!=null) sqlstr8 = " and department='"+department+"'";
		if (getChildLst!=null) sqlstr10 = " and organization_id in ("+getChildLst+")";
		                                
		if (paramName != null && paramValue != null) {
			if(paramName.equals("personnel_id")) sqlstr7 = " and personnel_id = "+paramValue;
			if(paramName.equals("personnel_name")) sqlstr7 = " and personnel_name like '%"+paramValue+"%'";
			if(paramName.equals("birthday")) sqlstr7 = " and birthday like '%"+paramValue+"%'";
			if(paramName.equals("personnel_sex")) sqlstr7 = " and personnel_sex like '%"+paramValue+"%'";
			if(paramName.equals("personnel_position")) sqlstr7 = " and personnel_position like '%"+paramValue+"%'";
			if(paramName.equals("organization_name")) sqlstr7 = " and organization_name like '%"+paramValue+"%'";
			if(paramName.equals("department")) sqlstr7 = " and department like '%"+paramValue+"%'";
		}
		if (organization_id!=0) sqlstr9 = " and organization_id="+organization_id;
		int totalSize = 0;

		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select count(*) as totalSize "
						+ "from personnel "
						+ "where personnel_id>0"+ sqlstr10 + sqlstr1 + sqlstr2 + sqlstr3 + sqlstr4 + sqlstr5+ sqlstr6 +sqlstr7 +sqlstr8 +sqlstr9;
			opera.prepStatment(sql);
			//opera.setString(1, getChildLst);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}
	
	public Personnel query1(int organization_id){
		Personnel personnel = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select getChildLst('"+organization_id+"')";
			opera.prepStatment(sql);
			//opera.setInt(1, organization_id);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				personnel = new Personnel();
				personnel.setChildLst(res.getString(1));
			}
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return personnel;
	}
	
	/**
	 * 
	 * @param personnel_id
	 * @return
	 */
	public Personnel query(int personnel_id){
		Personnel personnel = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select "

				+ "personnel_id,personnel_name,birthday,personnel_sex,personnel_phone,personnel_address,personnel_position," 
				+ "organization_id,organization_name,pid,organization_parent,add_name,add_time,updae_name,update_time,remarks," 
				+ "department,email,nation,political_status,educational_background,major,native_place,id_num,issuing_authority,account_id,account_name"
				+ " from personnel "
				+ " where personnel_id=?";
			opera.prepStatment(sql);
			opera.setInt(1, personnel_id);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				personnel = new Personnel();
				personnel.setPersonnel_id(res.getInt(1));
				personnel.setPersonnel_name(res.getString(2));
				personnel.setBirthday(res.getString(3));
				personnel.setPersonnel_sex(res.getString(4));
				personnel.setPersonnel_phone(res.getString(5));
				personnel.setPersonnel_address(res.getString(6));
				personnel.setPersonnel_position(res.getString(7));
				personnel.setOrganization_id(res.getInt(8));
				personnel.setOrganization_name(res.getString(9));
				personnel.setPid(res.getInt(10));
				personnel.setOrganization_parent(res.getString(11));
				personnel.setAdd_name(res.getString(12));
				personnel.setAdd_time(res.getString(13));
				personnel.setUpdae_name(res.getString(14));
				personnel.setUpdate_time(res.getString(15));
				personnel.setRemarks(res.getString(16));
				personnel.setDepartment(res.getString(17));
				personnel.setEmail(res.getString(18));
				personnel.setNation(res.getString(19));
				personnel.setPolitical_status(res.getString(20));
				personnel.setEducational_background(res.getString(21));
				personnel.setMajor(res.getString(22));
				personnel.setNative_place(res.getString(23));
				personnel.setId_num(res.getString(24));
				personnel.setIssuing_authority(res.getString(25));
				personnel.setAccount_id(res.getInt(26));
				personnel.setAccount_name(res.getString(27));
			}
		}catch(Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return personnel;
	}
	
	/**
	 * 
	 * @param personnel
	 * @return
	 */
	public boolean add(Personnel personnel){
    	boolean result = false;
    	try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "insert into personnel(personnel_name,birthday,personnel_sex,personnel_phone,personnel_address," 
				    + "personnel_position,organization_id,organization_name,pid,organization_parent,add_name,add_time," 
				    + "remarks,department,email,nation,political_status,educational_background,major,native_place,id_num,issuing_authority) " 
				    + "values (?,?,?,?,?,?,?,?,?,?,?,SYSDATE(),?,?,?,?,?,?,?,?,?,?) ";
			opera.prepStatment(sql);
			opera.setString(1, personnel.getPersonnel_name());
			opera.setString(2, personnel.getBirthday());
			opera.setString(3, personnel.getPersonnel_sex());
			opera.setString(4, personnel.getPersonnel_phone());
			opera.setString(5, personnel.getPersonnel_address());
			
			opera.setString(6, personnel.getPersonnel_position());
			opera.setInt(7, personnel.getOrganization_id());
			opera.setString(8, personnel.getOrganization_name());
			opera.setInt(9, personnel.getPid());
			opera.setString(10, personnel.getOrganization_parent());
			opera.setString(11, personnel.getAdd_name());
			opera.setString(12, personnel.getRemarks());
			
			opera.setString(13, personnel.getDepartment());
			opera.setString(14, personnel.getEmail());
			opera.setString(15, personnel.getNation());
			opera.setString(16, personnel.getPolitical_status());
			opera.setString(17, personnel.getEducational_background());
			opera.setString(18, personnel.getMajor());
			opera.setString(19, personnel.getNative_place());
			opera.setString(20, personnel.getId_num());
			opera.setString(21, personnel.getIssuing_authority());
			
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
    }
	
	/**
	 * 
	 * @param personnel
	 * @return
	 */
	public boolean edit(Personnel personnel){
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "update personnel "							
					+ "set personnel_name=?,birthday=?,personnel_sex=?,personnel_phone=?,personnel_address=?," 
				    + "personnel_position=?,organization_id=?,organization_name=?,pid=?,organization_parent=?,"
					+ "updae_name=?,update_time=SYSDATE(),remarks=?,department=?,email=?,nation=?,political_status=?," 
					+ "educational_background=?,major=?,native_place=?,id_num=?,issuing_authority=?,account_id=?,account_name=? "

					+ "where personnel_id=?";
			opera.prepStatment(sql);
			opera.setString(1, personnel.getPersonnel_name());
			opera.setString(2, personnel.getBirthday());
			opera.setString(3, personnel.getPersonnel_sex());
			opera.setString(4, personnel.getPersonnel_phone());
			opera.setString(5, personnel.getPersonnel_address());
			
			opera.setString(6, personnel.getPersonnel_position());
			opera.setInt(7, personnel.getOrganization_id());
			opera.setString(8, personnel.getOrganization_name());
			opera.setInt(9, personnel.getPid());
			opera.setString(10, personnel.getOrganization_parent());
			opera.setString(11, personnel.getUpdae_name());
			opera.setString(12, personnel.getRemarks());
			opera.setString(13, personnel.getDepartment());
			opera.setString(14, personnel.getEmail());
			opera.setString(15, personnel.getNation());
			opera.setString(16, personnel.getPolitical_status());
			opera.setString(17, personnel.getEducational_background());
			opera.setString(18, personnel.getMajor());
			opera.setString(19, personnel.getNative_place());
			opera.setString(20, personnel.getId_num());
			opera.setString(21, personnel.getIssuing_authority());
			opera.setInt(22, personnel.getAccount_id());
			opera.setString(23, personnel.getAccount_name());
			opera.setInt(24, personnel.getPersonnel_id());

			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}
	
	/**
	 * 
	 * @param personnel_id
	 * @return
	 */
	public Boolean delete(int personnel_id){
    	boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from personnel "
					+ "where personnel_id=? ";
			opera.prepStatment(sql);
			opera.setInt(1, personnel_id);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
    	
    }
	
	public Boolean deleteById(int organization_id){
    	boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from personnel "
					+ "where organization_id=? ";
			opera.prepStatment(sql);
			opera.setInt(1, organization_id);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
    	
    }
	public boolean edit1(int account_id,String account_name){
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "update personnel "							
					+ "set account_name=? " 
					+ "where account_id=?";
			opera.prepStatment(sql);
			opera.setString(1, account_name);
			opera.setInt(2, account_id);

			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}
	
}
